'''
@Project ：python-study
@File    ：pandas_def.py
@IDE     ：PyCharm
@Author  ：SUNLIN
@Date    ：2025/3/12 17:53:48
'''

import pandas as pd
import numpy as np

pd.set_option('display.expand_frame_repr', False)
pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
pd.set_option('display.width', 100)


def parse_date(date_list):
    date_list = list(date_list)  # 确保是副本，而不是视图
    for i, d in enumerate(date_list):
        if len(d) == 4 and d.isdigit():
            date_list[i] = f"{d}-01-01"
        if len(d) == 7:
            date_list[i] = f"{d}-01"
    return pd.to_datetime(date_list)


def movie_year_amount_tj(file):
    data = pd.read_excel(file)
    data['release_date'] = parse_date(data['release_date'])
    data = data.set_index(data['release_date'])
    data_year_tj = data['release_date'].resample('YE').count()
    return pd.DataFrame(data_year_tj)


def country_year_tj(file, usecols):
    data = pd.read_excel('../doc/datasource/C8-8.5-数据采集-clean.xlsx',
                         usecols=['movie_name', 'country', 'language', 'release_date', 'average_score', ])
    # 按照所定义顺序排序
    data = data[['movie_name', 'country', 'language', 'release_date', 'average_score']]

    data['country'] = data['country'].fillna(value='')
    data['country'] = data['country'].str.strip(' ')
    country_list = []
    for c in data['country']:
        c_list = c.split(' / ')
        for l in c_list:
            country_list.append(l)
    country_list = list(set(country_list))
    # 移除一些重复的数据
    if '美国/澳大利亚' in country_list:
        country_list.remove('美国/澳大利亚')
    if '捷克斯洛伐克/捷克' in country_list:
        country_list.remove('捷克斯洛伐克/捷克')
    if '中国大陆' in country_list:
        country_list.remove('中国大陆')
    if '中国香港' in country_list:
        country_list.remove('中国香港')
    if '中国台湾' in country_list:
        country_list.remove('中国台湾')

    data['release_date'] = parse_date(data['release_date'])
    data = data.set_index(data['release_date'])
    data_two_dimensional = pd.DataFrame(data['release_date'].resample('YE').count())
    data_two_dimensional = data_two_dimensional.drop(columns='release_date')
    for label in country_list:
        temp = data[data['country'].str.contains(label)]
        country_year_tj = temp['release_date'].resample('YE').count()
        data_two_dimensional[label] = country_year_tj
    data_two_dimensional = data_two_dimensional.fillna(value=0)
    return data_two_dimensional


def language_tj(file, usecols):
    data = pd.read_excel(file, usecols=usecols)
    data = data[usecols]
    data['language'] = data['language'].fillna(value='')
    data['language'] = data['language'].str.strip(' ')

    # 获取去重之后的语言列表
    language_list = []
    for c in data['language']:
        c_list = c.split(' / ')
        for l in c_list:
            language_list.append(l)
    language_list = list(set(language_list))
    if '' in language_list:
        language_list.remove('')
    if '汉语普通话' in language_list:
        language_list.remove('汉语普通话')

    data_lang_tj = pd.DataFrame(np.zeros([len(language_list), 1]), index=language_list, columns=['tj'])

    # 遍历方法1
    for c in language_list:
        for c1 in data['language']:
            if str(c1).__contains__(c):
                data_lang_tj.loc[c, 'tj'] += 1
    hnh_count = 0
    if '湖南话' in data_lang_tj:
        hnh_count = data_lang_tj['湖南话', 'tj']
    bjh_count = 0
    if '北京话' in data_lang_tj:
        bjh_count = data_lang_tj['北京话', 'tj']

    chinese_fy = hnh_count + bjh_count
    data_lang_tj.loc['中国方言', 'tj'] = chinese_fy
    return data_lang_tj


def average_votes(file, usecols):
    return pd.read_excel(file, usecols=usecols)


def type_tj(file):
    # 读取数据
    data = pd.read_excel(file)
    # 提取每一行的genre元素  -> 新的列表
    # 删掉左中括号
    data['type_list'] = data['type_list'].str.strip('[')
    # 删掉右中括号
    data['type_list'] = data['type_list'].str.strip(']')
    # 处理NaN
    data['type_list'] = data['type_list'].fillna(value='')
    genre_list = []
    for g in data['type_list']:
        g_list = g.split(', ')
        for l in g_list:
            l = l.strip("'")
            genre_list.append(l)
    # 给集合去重
    g_list = list(set(genre_list))
    # 去除空值
    # g_list.remove('')
    # 统计每个类型标签对应的电影数量、条数、频数
    data_genre_tj = pd.DataFrame(np.zeros([len(g_list), 1]), index=g_list, columns=['统计'])
    for i in data['type_list']:
        for label in g_list:
            if str(i).__contains__(label):
                data_genre_tj.loc[label, '统计'] += 1
    return data_genre_tj


file_path = 'D:\develop\projects\Python Project\python-study\doc\datasource\C8-8.5-数据采集-clean.xlsx'


def genre_rate_tj(x):
    '''
    :param x: 前x位
    :return: 排名前x位的电影类型标签，对应的评分均值数据
    '''
    # 电量类型(x):6个电影数量最多的标签
    # 评分数据(y):2,3,4,5,6,7,8,9,10
    # 电影数量(值)
    type_list = type_tj(file_path).sort_values('统计', ascending=False).head(x).index.tolist()
    print(type_list)
    rate_list = [2, 3, 4, 5, 6, 7, 8, 9, 10]
    data_type_list = pd.DataFrame(np.zeros([len(rate_list), len(type_list)]), index=rate_list, columns=type_list)
    data = pd.read_excel(file_path, usecols=['average_score', 'type_list'])

    for t in type_list:  # 循环电影类别的数据
        for r in rate_list:  # 循环评分的数据
            for i, rate in zip(data['type_list'], data['average_score']):
                if str(i).__contains__(t) and rate < r < rate + 1:
                    data_type_list.loc[r, t] += 1
    return data_type_list


def year_rate(file, usecols, year_list):
    data = pd.read_excel(file, usecols=usecols)
    data = data.set_index(pd.to_datetime(parse_date(data['release_date'])))
    tj = []

    for year in year_list:
        tj.append(data.loc[year]['average_score'].tolist())
    return tj


if __name__ == '__main__':
    data = year_rate(file_path, usecols=['movie_name', 'average_score', 'release_date'],
                     year_list=['2014', '2015'])
    print(data)
